﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
using System.Net.Sockets;
namespace UDPActiveMonitor
{
    sealed public class Business
    {

        private SqlDataAdapter sda;
        private SqlConnection sconn;
          
                private SqlDataAdapter sdaX;
                private SqlConnection sconnX;
        string svrConnStr;
        string stagingCS;
        //private SqlDataAdapter ssda;
        //private SqlConnection ssconn;
        private static Business instance;
        private string _CatalogProcessName;

        

        private string _ComputerName;
        public string ComputerName
        {
            get { return _ComputerName; }
            set { _ComputerName = value; }
        }
        private int _Catalogid;
        public int catalogid
        {
            get { return _Catalogid; }
            set { _Catalogid = value; }
        }
        public string CatalogProcessName
        {
            get { return _CatalogProcessName; }
            set { _CatalogProcessName = value; }
        }
  

        private string _Field2;
        public string Field2
        {
            get { return _Field2; }
            set { _Field2 = value; }
        }
        private string _Field1;
        public string Field1
        {
            get { return _Field1; }
            set { _Field1 = value; }
        }


    

        public static Business Instance
        {

            get
            {
                if (instance == null)
                {

                    instance = new Business();
                    instance.ComputerName = "NotSet";
                    instance.svrConnStr = "data source=(local);packet size=4096;integrated security=SSPI;persist security info=False;initial catalog=humdum";
                    //instance.svrConnStr = "data source=dndnhumtdumt;packet size=4096;integrated security=SSPI;persist security info=False;initial catalog=humdum";
                    instance.stagingCS = "data source=dndnhumtdumt;packet size=4096;integrated security=SSPI;persist security info=False;initial catalog=humdum";
                    instance.stagingCS = "data source=(local);packet size=4096;integrated security=SSPI;persist security info=False;initial catalog=humdum";
                    instance.BypassInitialLoad = false;
                    instance.test = "test";
                }
                return instance;
            }
        }
        private string _Test;
        public string test
        {
            get { return _Test; }
            set { _Test = value; }
        }
   
        private string _FileNameSuffix;
        public string FileNameSuffix
        {
            get { return _FileNameSuffix; }
            set { _FileNameSuffix = value; }
        }

        private string _CurrentFileName;
        public string CurrentFileName
        {
            get { return _CurrentFileName; }
            set { _CurrentFileName = value; }
        }

        public string RunSqlProc(string Name, string Param, string paramv)
        {
           
            if (sda.UpdateCommand == null)
                sda.UpdateCommand = new SqlCommand();

            sda.UpdateCommand.Connection = sconn;
            sda.UpdateCommand.CommandText = Name;
            sda.UpdateCommand.CommandType = CommandType.StoredProcedure;
            sda.UpdateCommand.Parameters.Clear();
            if (Param.Length > 0)
            {
                sda.UpdateCommand.Parameters.Add(Param, SqlDbType.NVarChar, 50);
                sda.UpdateCommand.Parameters[Param].Value = paramv;
            }

            if (sconn.State == ConnectionState.Closed)
            {
                sconn.Open();
            }

            try
            {
                sda.UpdateCommand.ExecuteNonQuery();
            }
            catch (Exception exx)
            {
                string m = exx.Message;
                return m;
            }
            sconn.Close();
            sda.UpdateCommand.Parameters.Clear();
            sda.UpdateCommand.CommandType = CommandType.Text;
            return "";

        }

        public string RunSql(string sql, ref DataTable dt,bool ShowError)
        {
            dt.Clear();
            if (sda == null)
            {
                sda = new SqlDataAdapter();
            }
            if (sda.SelectCommand == null)
            {

                sda.SelectCommand = new SqlCommand();

                sconn = new SqlConnection(instance.svrConnStr);
                sda.SelectCommand.CommandTimeout = 600;
                sda.SelectCommand.Connection = sconn;
            }
            sda.SelectCommand.CommandText = sql;
              
                sda.Fill(dt);
          
            return "";
        }
        public string RunSql(string sql, ref DataTable dt)
        {
            dt.Clear();
            if (sda == null)
            {
                sda = new SqlDataAdapter();
            }
            if (sda.SelectCommand == null)
            {

                sda.SelectCommand = new SqlCommand();

                sconn = new SqlConnection(instance.svrConnStr);
                sda.SelectCommand.CommandTimeout = 600;
                sda.SelectCommand.Connection = sconn;
            }
            sda.SelectCommand.CommandText = sql;
            try
            {
                sda.Fill(dt);
            }
            catch (Exception exx) 
            {
                udpm("Error: " + exx.Message);
                throw new Exception("Error sql " + exx.Message);
            }
            return "";
        }
        public string RunSqlX(string sql, ref DataTable dt)
        {
            dt.Clear();
            if (sdaX == null)
            {
                sdaX = new SqlDataAdapter();
            }
            if (sdaX.SelectCommand == null)
            {
                sdaX.SelectCommand = new SqlCommand();
                sconnX = new SqlConnection(instance.stagingCS);
                sdaX.SelectCommand.CommandTimeout = 600;
                sdaX.SelectCommand.Connection = sconnX;
            }
            sdaX.SelectCommand.CommandText = sql;
            try
            {
                sdaX.Fill(dt);
            }
            catch (Exception exx)
            {
                udpm("Error: " + exx.Message);
                throw new Exception("Error sql " + exx.Message);
            }
            return "";
        }

        private bool _UseProductAttribute;
        public bool UseProductAttribute
        {
            get { return _UseProductAttribute; }
            set { _UseProductAttribute = value; }
        }
        private string _ErrorMsg;
        public string ErrorMsg
        {
            get { return _ErrorMsg; }
            set { _ErrorMsg = value; }
        }
        private bool _UseEngine;
        public bool useEngine
        {
            get { return _UseEngine; }
            set { _UseEngine = value; }
        }

        private bool _EmailNotify;
        public bool EmailNotify
        {
            get { return _EmailNotify; }
            set { _EmailNotify = value; }
        }
        private int _EngineSeq;
        public int EngineSeq
        {
            get { return _EngineSeq; }
            set { _EngineSeq = value; }
        }
        private int _YrSeq;
        public int YrSeq
        {
            get { return _YrSeq; }
            set { _YrSeq = value; }
        }
        public string StripComment(string startswith, string comment)
        {
            string res;
            string[] sp;
            if (comment.StartsWith(startswith))
            {
                sp = comment.Split(';');
                res = "";
                for (int i = 1; i < sp.Length; i++)
                {
                    res += sp[i] + "; ";
                }
                if (sp.Length > 1)
                    res = res.Remove(res.Length - 2);
            }
            else
                res = comment;
            return res;
        }
        private int _LogLevel;
        public int LogLevel
        {
            get { return _LogLevel; }
            set { _LogLevel = value; }
        }
        private string _ClientIP;
        public string ClientIP
        {
        	get {	return _ClientIP; }
        	set { _ClientIP = value;	}
        }
        private string _UserEmail;
        public string UserEmail
        {
            get { return _UserEmail; }
            set { _UserEmail = value; }
        }
        string sql;
        public void PlaceCatalogInQueue(string catalog,string useremail,string cip,string LogLevel)
        {

            sql = "insert into pub_Queue (CatalogName,Status,useremail,DateSubmitted,qerror,completed,clientip,LogLevel)";
            sql += "value ('" + catalog + "',2,'" + useremail + "',getdate(),'not set',0,'" + cip + "'," + LogLevel+ ")";
            RunSql(sql);

        }
        public string DoSql(string sql)
        {
            return RunSql(sql);
        }
        public string RunSql(string sql)
        {

            if (sda == null)
            {
                sda = new SqlDataAdapter();
            }
            if (sda.UpdateCommand == null)
            {
                sda.UpdateCommand = new SqlCommand();

                sconn = new SqlConnection(instance.svrConnStr);
                sconn.Open();
                sda.UpdateCommand.Connection = sconn;
                sda.UpdateCommand.CommandTimeout = 800;
            }
            sda.UpdateCommand.CommandText = sql;
            try
            {
                if (sda.UpdateCommand.Connection.State == ConnectionState.Closed)
                    sda.UpdateCommand.Connection.Open();
                sda.UpdateCommand.ExecuteNonQuery();
            }
            catch (Exception ex1)
            {
                if (ex1.Message.IndexOf("native frame") > 0)
                {
                    sda.Dispose();
                    sda = new SqlDataAdapter();
                    sda.UpdateCommand = new SqlCommand();

                    sconn = new SqlConnection(instance.svrConnStr);
                    sconn.Open();
                    sda.UpdateCommand.Connection = sconn;
                    sda.UpdateCommand.CommandText = sql;
                    sda.UpdateCommand.ExecuteNonQuery();
                }
                throw new Exception(sql);
            }
            return "";
        }
        public void SetConnection(bool canada)
        {
            if (canada)
            {
                svrConnStr = "data source=bcms2008;packet size=4096;integrated security=SSPI;persist security info=False;initial catalog=partstrakCA";
            }
            else
                svrConnStr = "Data Source=dnmsscopeapp;Initial Catalog=PartsTrakUS;User ID=partstrak;password=partstrak";
        }

        private string _NumericCatalogName;
        public string NumericCatalogName
        {
            get { return _NumericCatalogName; }
            set { _NumericCatalogName = value; }
        }
        static public bool comparestringClose(string s1, string s2)
        {



            bool cmp = false;

            if (s2.StartsWith(s1.Substring(0, s1.Length - 1)))
                cmp = true;
            if (s1.Substring(0, s1.Length - 1) == s2)
                cmp = true;
            if (s2.Substring(0, s2.Length - 1) == s1)
                cmp = true;
            if (s1 == s2)
                cmp = true;

            return cmp;

        }
        private bool _SingleAttribute;
        public bool SingleAttribute
        {
            get { return _SingleAttribute; }
            set { _SingleAttribute = value; }
        }
        private bool _UseComment;
        public bool UseComment
        {
            get { return _UseComment; }
            set { _UseComment = value; }
        }
        private bool _UseGrpRecord;
        public bool UseGrpRecord
        {
            get { return _UseGrpRecord; }
            set { _UseGrpRecord = value; }
        }
        private bool _UseSysRecord;
        public bool UseSysRecord
        {
            get { return _UseSysRecord; }
            set { _UseSysRecord = value; }
        }

        private bool _UseRealApps;
        public bool UseRealApps
        {
            get { return _UseRealApps; }
            set { _UseRealApps = value; }
        }

        private bool _BypassInitialLoad;
        public bool BypassInitialLoad
        {
            get { return _BypassInitialLoad; }
            set { _BypassInitialLoad = value; }
        }
        public List<string> PartlistForSYMBOL;

       /*
        public void partlistforsymbol()
        {

            oconn = new OleDbConnection();
            oconn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\apa\humdum.accdb;User ID=Admin;Password=APAadmin;";
            //oconn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\apa\apa.mdb;User Id=admin;Password=APAadmin;";
            oconn.ConnectionString = "DSN=myDsn;Uid=admin;Pwd=APAadmin;";
            oda = new OleDbDataAdapter();
            oda.SelectCommand = new OleDbCommand();
            oda.SelectCommand.Connection = oconn;

            DataTable dtSql = new DataTable();
            RunSql("select * from pub_sql", ref dtSql);
            string osql = dtSql.Rows[0]["SQL"].ToString();

            oda.SelectCommand.CommandText = osql;
            DataTable partlist = new DataTable();
            try
            {
                oda.Fill(partlist);
            }
            catch (Exception exx)
            {
                string mess = exx.Message;
            }

        }
     */

        public int GetColumnIndex(string col,DataTable dt)
        {

            int idx = -1;
            int idxcnt = 0;
            foreach (DataColumn dc in dt.Columns)
            {
                if (dc.ColumnName.ToLower() == col.ToLower())
                {
                    idx = idxcnt;
                }
                idxcnt++;
            }

            return idx;
        }
        public void udpm(string message)
        {
            
            UdpClient udpc = new UdpClient("10.15.82.210", 7785);
            byte[] bite = new byte[message.Length + _ComputerName.Length + 3];
            int ii = 0;
            foreach (byte bt in message)
            {
                bite[ii] = bt;
                ii++;
            }
            bite[ii] = 32;
            ii++;
            bite[ii] = 64;
            ii++;
            bite[ii] = 32;
            ii++;
            foreach (byte bt in _ComputerName)
            {
                bite[ii] = bt;
                ii++;
            }
            udpc.Send(bite, bite.Length);
        }

        
    }
}
